#delim cr
set more off
*version 11
pause on
graph set ps logo off

capture log close
set linesize 80
set logtype text
log using ../log/import-excel-files.log , replace

/* --------------------------------------

Bring in all of the translated
Excel files.

--------------------------------------- */

clear all
estimates clear


************************************************************
**   Bring in hotel data
************************************************************

********THIS IS THE HOTEL DATA********

import excel "../src/TranslatedCTRIP/tHTL_experiment(2010.1.1-2011.8.31).xls", sheet("data") firstrow
save "temp_HTL(2010.1.1-2011.8.31)", replace

clear
import excel "../src/TranslatedCTRIP/tHTL_non(2010.1.1-6.30).xls", sheet("data") firstrow
save "temp_HTL(2010.1.1-6.30)", replace

clear
import excel "../src/TranslatedCTRIP/tHTL_non(2010.7.1-12.31).xls", sheet("data") firstrow
save "temp_HTL(2010.7.1-12.31)", replace

clear
import excel "../src/TranslatedCTRIP/tHTL_non(2011.1.1-7.17).xls", sheet("data") firstrow
save "temp_HTL(2011.1.1-7.17)", replace

clear
import excel "../src/TranslatedCTRIP/tHTL_non(2011.5.16-7.18).xls", sheet("data") firstrow
save "temp_HTL(2011.5.16-7.18)", replace

clear
import excel "../src/TranslatedCTRIP/tHTL_non(2011.7.18-8.31).xls", sheet("data") firstrow
save "temp_HTL(2011.7.18-8.31)", replace

clear
import excel "../src/TranslatedCTRIP/thotelWorkFromHomeDailyDataInquiry(9.1-9.15).xls", sheet("data") firstrow
save "temp_HTL(9.1-9.15)", replace

clear
import excel "../src/TranslatedCTRIP/thotelWorkFromHomeDailyDataInquiry(9.16-9.30).xls", sheet("data") firstrow
save "temp_HTL(9.16-9.30)", replace

clear
import excel "../src/TranslatedCTRIP/thotelWorkFromHomeDailyDataInquiry(10.1-10.15).xls", sheet("data") firstrow
save "temp_HTL(10.1-10.15)", replace

clear
import excel "../src/TranslatedCTRIP/thotelWorkFromHomeDailyDataInquiry(10.16-10.31).xls", sheet("data") firstrow
save "temp_HTL(10.16-10.31)", replace

clear
import excel "../src/TranslatedCTRIP/thotelWorkFromHomeDailyDataInquiry(11.1-11.15).xls", sheet("data") firstrow
save "temp_HTL(11.1-11.15)", replace

clear
import excel "../src/TranslatedCTRIP/thotelWorkFromHomeDailyDataInquiry(11.16-11.30).xls", sheet("data") firstrow
save "temp_HTL(11.16-11.30)", replace

clear
import excel "../src/TranslatedCTRIP/thotelWorkFromHomeDailyDataInquiry(12.1-12.25).xls", sheet("data") firstrow
save "temp_HTL(12.1-12.25)", replace

clear
import excel "../src/TranslatedCTRIP/thotelWorkFromHomeDailyDataInquiry(12.26-2012.1.31).xls", sheet("data") firstrow
save "temp_HTL(12.26-2012.1.31)", replace

clear
import excel "../src/TranslatedCTRIP/thotelWorkFromHomeDailyDataInquiry(2012.2.1-2.29).xls", sheet("data") firstrow
save "temp_HTL(2012.2.1-2.29)", replace

clear
import excel "../src/TranslatedCTRIP/thotelWorkFromHomeDailyDataInquiry(2012.3.1-3.31).xls", sheet("data") firstrow
save "temp_HTL(2012.3.1-3.31)", replace

clear
import excel "../src/TranslatedCTRIP/thotelWorkFromHomeDailyDataInquiry(2012.4.1-4.30).xls", sheet("data") firstrow
save "temp_HTL(2012.4.1-4.30)", replace

clear
import excel "../src/TranslatedCTRIP/thotelWorkFromHomeDailyDataInquiry(2012.5.1-5.31).xls", sheet("data") firstrow
save "temp_HTL(2012.5.1-5.31)", replace

clear
import excel "../src/TranslatedCTRIP/thotelWorkFromHomeDailyDataInquiry(2012.6.1-6.30).xls", sheet("data") firstrow
save "temp_HTL(2012.6.1-6.30)", replace

clear
import excel "../src/TranslatedCTRIP/thotelWorkFromHomeDailyDataInquiry(2012.7.1-7.31).xls", sheet("data") firstrow
save "temp_HTL(2012.7.1-7.31).", replace

clear
import excel "../src/TranslatedCTRIP/thotelWorkFromHomeDailyDataInquiry(2012.8.1-8.31).xls", sheet("data") firstrow
save "temp_HTL(2012.8.1-8.31)", replace

clear
import excel "../src/TranslatedCTRIP/thotelWorkFromHomeDailyDataInquiry(2012.9.1-9.30).xls", sheet("data") firstrow
save "temp_HTL(2012.9.1-9.30)", replace


clear
import excel "../src/TranslatedCTRIP/thotelWorkFromHomeDailyDataInquiry(2012.10.1-2012.11.30).xls", sheet("data1") firstrow
save "temp_HTL(2012.10.1-2012.11.30)1", replace

clear
import excel "../src/TranslatedCTRIP/thotelWorkFromHomeDailyDataInquiry(2012.10.1-2012.11.30).xls", sheet("data2") firstrow
save "temp_HTL(2012.10.1-2012.11.30)2", replace


clear
import excel "../src/TranslatedCTRIP/thotelWorkFromHomeDailyDataInquiry(2012.12.1-2013.1.31).xls", sheet("data1") firstrow
save "temp_HTL(2012.12.1-2013.1.31)1", replace

clear
import excel "../src/TranslatedCTRIP/thotelWorkFromHomeDailyDataInquiry(2012.12.1-2013.1.31).xls", sheet("data2") firstrow
save "temp_HTL(2012.12.1-2013.1.31)2", replace


clear
import excel "../src/TranslatedCTRIP/thotelWorkFromHomeDailyDataInquiry(2013.2.1-2013.4.30).xls", sheet("data1") firstrow
save "temp_HTL(2013.2.1-2013.4.30)1", replace

clear
import excel "../src/TranslatedCTRIP/thotelWorkFromHomeDailyDataInquiry(2013.2.1-2013.4.30).xls", sheet("data2") firstrow
save "temp_HTL(2013.2.1-2013.4.30)2", replace

clear
import excel "../src/TranslatedCTRIP/thotelWorkFromHomeDailyDataInquiry(2013.2.1-2013.4.30).xls", sheet("data3") firstrow
save "temp_HTL(2013.2.1-2013.4.30)3", replace


use "temp_HTL(2010.1.1-2011.8.31)"
**rename NumTicker NumTicket
**rename TickerScore TicketScore
**rename WEEK week

append using "temp_HTL(2010.1.1-6.30)"
append using "temp_HTL(2010.7.1-12.31)"
append using "temp_HTL(2011.1.1-7.17)"
append using "temp_HTL(2011.5.16-7.18)"
append using "temp_HTL(2011.7.18-8.31)"
append using "temp_HTL(9.1-9.15)"
append using "temp_HTL(9.16-9.30)"
append using "temp_HTL(10.1-10.15)"
append using "temp_HTL(10.16-10.31)"
append using "temp_HTL(11.1-11.15)"
append using "temp_HTL(11.16-11.30)"
append using "temp_HTL(12.1-12.25)"
append using "temp_HTL(12.26-2012.1.31)"
append using "temp_HTL(2012.2.1-2.29)"
append using "temp_HTL(2012.3.1-3.31)"
append using "temp_HTL(2012.4.1-4.30)"
append using "temp_HTL(2012.5.1-5.31)"
append using "temp_HTL(2012.6.1-6.30)"
append using "temp_HTL(2012.7.1-7.31)."
append using "temp_HTL(2012.8.1-8.31)"
append using "temp_HTL(2012.9.1-9.30)"
append using "temp_HTL(2012.10.1-2012.11.30)1"
append using "temp_HTL(2012.10.1-2012.11.30)2"
append using "temp_HTL(2012.12.1-2013.1.31)1"
append using "temp_HTL(2012.12.1-2013.1.31)2"
append using "temp_HTL(2013.2.1-2013.4.30)1"
append using "temp_HTL(2013.2.1-2013.4.30)2"
append using "temp_HTL(2013.2.1-2013.4.30)3"


*** Fixing some variations in Lables in the translated EXCEL spreadsheets ***

replace RecommendScore=OrderCompletedRecomScore if RecommendScore==.
drop OrderCompletedRecomScore
drop O

save ../dta/ctripHTL.dta, replace

************************************************************
**   Bring in the FLT data
************************************************************

clear

********THIS IS THE FLT DATA********

import excel "../src/TranslatedCTRIP/tFLT-experiment(2010.1.1-2011.8.31).xls", sheet("FLT") firstrow
save "temp_FLT(2010.1.1-2011.8.31)", replace
clear

import excel "../src/TranslatedCTRIP/tflt-nonexperiment(2010.1.1-6.30).xls", sheet("FLT") firstrow
save "temp_FLT(2010.1.1-6.30)", replace
clear

import excel "../src/TranslatedCTRIP/tflt-nonexperiment(2010.7.1-12.31).xls", sheet("FLT") firstrow
save "temp_FLT(2010.7.1-12.31)", replace
clear

import excel "../src/TranslatedCTRIP/tflt-nonexperiment(2011.1.1-7.17).xls", sheet("FLT") firstrow
save "temp_FLT(2011.1.1-7.17)", replace
clear

import excel "../src/TranslatedCTRIP/tflt-nonexperiment(2011.5.16-7.18).xls", sheet("FLT") firstrow
save "temp_FLT(2011.5.16-7.18)", replace
clear

import excel "../src/TranslatedCTRIP/tflt-nonexperiment(2011.7.18-8.31).xls", sheet("FLT") firstrow
save "temp_FLT(2011.7.18-8.31)", replace
clear


import excel "../src/TranslatedCTRIP/tflightTicketEmployee_numCall_numTicketData_Notification(9.1-9.30).xls", sheet("FLT") firstrow
save "temp_FLT(9.1-9.30)", replace
clear

import excel "../src/TranslatedCTRIP/tflightTicketEmployee_numCall_numTicketData_Notification(10.1-10.31).xls", sheet("FLT") firstrow
save "temp_FLT(10.1-10.31)", replace
clear

import excel "../src/TranslatedCTRIP/tflightTicketEmployee_numCall_numTicketData_Notification(11.1-11.30).xls", sheet("FLT") firstrow
save "temp_FLT(11.1-11.30)", replace
clear

import excel "../src/TranslatedCTRIP/tflightTicketEmployee_numCall_numTicketData_Notification(12.1-12.25).xls", sheet("FLT") firstrow
save "temp_FLT(12.1-12.25)", replace
clear

import excel "../src/TranslatedCTRIP/tflightTicketEmployee_numCall_numTicketData_Notification(12.26-2012.1.12).xls", sheet("FLT") firstrow
save "temp_FLT(12.26-2012.1.12)", replace
clear

import excel "../src/TranslatedCTRIP/tflightTicketEmployee_numCall_numTicketData_Notification(2012.1.13-1.31).xls", sheet("FLT") firstrow
save "temp_FLT(2012.1.13-1.31)", replace
clear

import excel "../src/TranslatedCTRIP/tflightTicketEmployee_numCall_numTicketData_Notification(2012.2.2-2.29).xls", sheet("FLT") firstrow
save "temp_FLT(2012.2.2-2.29)", replace
clear

import excel "../src/TranslatedCTRIP/tflightTicketEmployee_numCall_numTicketData_Notification(2012.3.1-3.31).xls", sheet("FLT") firstrow
save "temp_FLT(2012.3.1-3.31)", replace
clear

import excel "../src/TranslatedCTRIP/tflightTicketEmployee_numCall_numTicketData_Notification(2012.4.1-4.30).xls", sheet("FLT") firstrow
save "temp_FLT(2012.4.1-4.30)", replace
clear

import excel "../src/TranslatedCTRIP/tflightTicketEmployee_numCall_numTicketData_Notification(2012.5.1-5.31).xls", sheet("FLT") firstrow
save "temp_FLT(2012.5.1-5.31)", replace
clear

import excel "../src/TranslatedCTRIP/tflightTicketEmployee_numCall_numTicketData_Notification(2012.6.1-6.30).xls", sheet("FLT") firstrow
save "temp_FLT(2012.6.1-6.30)", replace
clear

import excel "../src/TranslatedCTRIP/tflightTicketEmployee_numCall_numTicketData_Notification(2012.7.1-7.18).xls", sheet("FLT") firstrow
save "temp_FLT(2012.7.1-7.18)", replace
clear

import excel "../src/TranslatedCTRIP/tflightTicketEmployee_numCall_numTicketData_Notification(2012.7.19-7.31).xls", sheet("FLT") firstrow
save "temp_FLT(2012.7.19-7.31)", replace
clear

import excel "../src/TranslatedCTRIP/tflightTicketEmployee_numCall_numTicketData_Notification(2012.8.1-8.18).xls", sheet("FLT") firstrow
save "temp_FLT(2012.8.1-8.18)", replace
clear

import excel "../src/TranslatedCTRIP/tflightTicketEmployee_numCall_numTicketData_Notification(2012.8.19-8.31).xls", sheet("FLT") firstrow
save "temp_FLT(2012.8.19-8.31)", replace
clear

import excel "../src/TranslatedCTRIP/tflightTicketEmployee_numCall_numTicketData_Notification(2012.9.1-9.18).xls", sheet("FLT") firstrow
save "temp_FLT(2012.9.1-9.18)", replace
clear

import excel "../src/TranslatedCTRIP/tflightTicketEmployee_numCall_numTicketData_Notification(2012.9.19-9.30).xls", sheet("FLT") firstrow
save "temp_FLT(2012.9.19-9.30)", replace
clear

import excel "../src/TranslatedCTRIP/tflightTicketEmployee_numCall_numTicketData_Notification(2012.10.1-2013.4.30).xls", sheet("Sheet 1") firstrow
save "temp_FLT(2012.10.1-2013.4.30)a", replace
clear

import excel "../src/TranslatedCTRIP/tflightTicketEmployee_numCall_numTicketData_Notification(2012.10.1-2013.4.30).xls", sheet("Sheet 1(1)") firstrow
save "temp_FLT(2012.10.1-2013.4.30)b", replace
clear

import excel "../src/TranslatedCTRIP/tflightTicketEmployee_numCall_numTicketData_Notification(2012.10.1-2013.4.30).xls", sheet("Sheet 1(2)") firstrow
save "temp_FLT(2012.10.1-2013.4.30)c", replace
clear

import excel "../src/TranslatedCTRIP/tflightTicketEmployee_numCall_numTicketData_Notification(2012.10.1-2013.4.30).xls", sheet("Sheet 1(3)") firstrow
save "temp_FLT(2012.10.1-2013.4.30)d", replace
clear

import excel "../src/TranslatedCTRIP/tflightTicketEmployee_numCall_numTicketData_Notification(2012.10.1-2013.4.30).xls", sheet("Sheet 1(4)") firstrow
save "temp_FLT(2012.10.1-2013.4.30)e", replace
clear

import excel "../src/TranslatedCTRIP/tflightTicketEmployee_numCall_numTicketData_Notification(2012.10.1-2013.4.30).xls", sheet("Sheet 1(5)") firstrow
save "temp_FLT(2012.10.1-2013.4.30)f", replace
clear

import excel "../src/TranslatedCTRIP/tflightTicketEmployee_numCall_numTicketData_Notification(2012.10.1-2013.4.30).xls", sheet("Sheet 1(6)") firstrow
save "temp_FLT(2012.10.1-2013.4.30)g", replace
clear

import excel "../src/TranslatedCTRIP/tflightTicketEmployee_numCall_numTicketData_Notification(2012.10.1-2013.4.30).xls", sheet("Sheet 1(7)") firstrow
save "temp_FLT(2012.10.1-2013.4.30)h", replace
clear

import excel "../src/TranslatedCTRIP/tflightTicketEmployee_numCall_numTicketData_Notification(2012.10.1-2013.4.30).xls", sheet("Sheet 1(8)") firstrow
save "temp_FLT(2012.10.1-2013.4.30)i", replace
clear

*** MERGING INTO ONE FILE ***

use "temp_FLT(2010.1.1-2011.8.31)"
rename NumTicker NumTicket
rename TickerScore TicketScore
rename WEEK week
append using "temp_FLT(2010.1.1-6.30)"
append using "temp_FLT(2010.7.1-12.31)"
append using "temp_FLT(2011.1.1-7.17)"
append using "temp_FLT(2011.5.16-7.18)"
append using "temp_FLT(2011.7.18-8.31)"
append using "temp_FLT(9.1-9.30)"
append using "temp_FLT(10.1-10.31)"
append using "temp_FLT(11.1-11.30)"
append using "temp_FLT(12.1-12.25)"
append using "temp_FLT(12.26-2012.1.12)"
append using "temp_FLT(2012.1.13-1.31)"
append using "temp_FLT(2012.2.2-2.29)"
append using "temp_FLT(2012.3.1-3.31)"
append using "temp_FLT(2012.4.1-4.30)"
append using "temp_FLT(2012.5.1-5.31)"
append using "temp_FLT(2012.6.1-6.30)"
append using "temp_FLT(2012.7.1-7.18)"
append using "temp_FLT(2012.7.19-7.31)"
append using "temp_FLT(2012.8.1-8.18)"
append using "temp_FLT(2012.8.19-8.31)"
append using "temp_FLT(2012.9.1-9.18)"
append using "temp_FLT(2012.9.19-9.30)"

append using "temp_FLT(2012.10.1-2013.4.30)a"
append using "temp_FLT(2012.10.1-2013.4.30)b"
append using "temp_FLT(2012.10.1-2013.4.30)c"
append using "temp_FLT(2012.10.1-2013.4.30)d"
append using "temp_FLT(2012.10.1-2013.4.30)e"
append using "temp_FLT(2012.10.1-2013.4.30)f"
append using "temp_FLT(2012.10.1-2013.4.30)g"
append using "temp_FLT(2012.10.1-2013.4.30)h"
append using "temp_FLT(2012.10.1-2013.4.30)i"


*** Fixing some variations in Lables in the translated EXCEL spreadsheets ***
replace NumTicket=numTicketedTrip if NumTicket==.
drop numTicketedTrip

replace TicketScore=ticketScore if TicketScore==.
drop ticketScore

replace NumNotification=numNotification if NumNotification==.
drop numNotification

replace NumInsurance=numInsurance if NumInsurance==.
drop numInsurance

replace RecommendScore=recommendScore if RecommendScore==.
drop recommendScore

save ../dta/ctripFLT.dta, replace

log close
exit
